﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Data.Common;
using System.Threading.Tasks;
using System.Globalization;
using System.Text;

namespace Moon.Sql
{
    public abstract class DatabaseProvider
    {
        protected IDbConnection _Connection { get; set; }
        protected IDbTransaction _Transaction { get; set; }
        protected IAop _Aop { get; set; }

        public DatabaseProvider()
        {
        }

        protected virtual ExecutingArgs OnExecuting(string sql, object param, ExecuteType executeType)
        {
            if (_Aop != null && _Aop.OnExecuting != null)
            {
                ExecutingArgs executingArgs = new ExecutingArgs(sql, param, ParamToString(param))
                {
                    LogId = Guid.NewGuid().ToString("N"),
                    ExecuteType = executeType,
                };
                _Aop.OnExecuting(executingArgs);
                return executingArgs;
            }
            return null;
        }

        protected virtual ExecutedArgs OnExecuted(ExecutingArgs args, Exception exception, object executeResult)
        {
            if (_Aop != null && _Aop.OnExecuted != null)
            {
                var executedArgs = new ExecutedArgs(args, exception, executeResult)
                {
                    LogId = args.LogId,
                    ExecuteType = args.ExecuteType
                };
                _Aop.OnExecuted(executedArgs);
                return executedArgs;
            }
            return null;
        }

        public IDbConnection OpenConnection()
        {
            if (_Connection.State == ConnectionState.Closed)
                _Connection.Open();
            return _Connection;
        }

        public void CloseConnection()
        {
            if (_Connection.State == ConnectionState.Open)
                _Connection.Close();
        }

        public virtual GridReader QueryMultiple(string sql, object param = null, CommandType? commandType = null)
        {
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    var reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
                    wasClosed = false;
                    return new GridReader(reader);
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
        }

        public virtual async Task<GridReader> QueryMultipleAsync(string sql, object param = null, CommandType? commandType = null)
        {
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    var reader = await (cmd as DbCommand).ExecuteReaderAsync(CommandBehavior.CloseConnection);
                    wasClosed = false;
                    return new GridReader(reader);
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
        }

        public virtual List<T> Query<T>(string sql, object param = null, CommandType? commandType = null)
        {
            List<T> result = new List<T>();
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        result = DataMapper.GetDataReader<T>(reader);
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
            return result;
        }

        public virtual async Task<List<T>> QueryAsync<T>(string sql, object param = null, CommandType? commandType = null)
        {
            List<T> result = new List<T>();
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    using (var reader = await (cmd as DbCommand).ExecuteReaderAsync())
                    {
                        result = await DataMapper.GetDataReaderAsync<T>(reader);
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
            return result;
        }

        public virtual T QueryFirst<T>(string sql, object param = null, CommandType? commandType = null)
        {
            T result = default(T);
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        result = DataMapper.GetDataReaderFirst<T>(reader);
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
            return result;
        }

        public virtual async Task<T> QueryFirstAsync<T>(string sql, object param = null, CommandType? commandType = null)
        {
            T result = default(T);
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    using (var reader = await (cmd as DbCommand).ExecuteReaderAsync())
                    {
                        result = await DataMapper.GetDataReaderFirstAsync<T>(reader);
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
            return result;
        }

        public virtual QueryPageResult<T> QueryPage<T>(string sql, object param = null)
        {
            QueryPageResult<T> result = new QueryPageResult<T>();
            using (var grid = QueryMultiple(sql, param))
            {
                result.total = grid.ReadFirst<int>();
                result.rows = grid.Read<T>();
            }
            return result;
        }

        public virtual async Task<QueryPageResult<T>> QueryPageAsync<T>(string sql, object param = null)
        {
            QueryPageResult<T> result = new QueryPageResult<T>();
            using (var grid = await QueryMultipleAsync(sql, param))
            {
                result.total = await grid.ReadFirstAsync<int>();
                result.rows = await grid.ReadAsync<T>();
            }
            return result;
        }

        public virtual IDataReader ExecuteReader(string sql, object param = null, CommandType? commandType = null)
        {
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    wasClosed = false;
                    return cmd.ExecuteReader(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
        }

        public virtual async Task<IDataReader> ExecuteReaderAsync(string sql, object param = null, CommandType? commandType = null)
        {
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    wasClosed = false;
                    return await (cmd as DbCommand).ExecuteReaderAsync(CommandBehavior.CloseConnection);
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
        }

        public virtual DataTable Query(string sql, object param = null, CommandType? commandType = null)
        {
            DataTable table = new DataTable();
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    using (var reader = cmd.ExecuteReader())
                    {
                        table.Load(reader);
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
            return table;
        }

        public virtual async Task<DataTable> QueryAsync(string sql, object param = null, CommandType? commandType = null)
        {
            DataTable table = new DataTable();
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    using (var reader = await (cmd as DbCommand).ExecuteReaderAsync())
                    {
                        table.Load(reader);
                    }
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
            return table;
        }

        public virtual int Execute(string sql, object param = null, CommandType? commandType = null)
        {
            int rowcount = 0;
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.NonQuery);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    rowcount = cmd.ExecuteNonQuery();
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, rowcount);
            }
            return rowcount;
        }

        public virtual async Task<int> ExecuteAsync(string sql, object param = null, CommandType? commandType = null)
        {
            int rowcount = 0;
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.NonQuery);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    rowcount = await (cmd as DbCommand).ExecuteNonQueryAsync();
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, rowcount);
            }
            return rowcount;
        }

        public virtual T ExecuteScalar<T>(string sql, object param = null, CommandType? commandType = null)
        {
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    object o = cmd.ExecuteScalar();
                    return DataMapper.Parse<T>(o);
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
        }

        public virtual async Task<T> ExecuteScalarAsync<T>(string sql, object param = null, CommandType? commandType = null)
        {
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    object o = await (cmd as DbCommand).ExecuteScalarAsync();
                    return DataMapper.Parse<T>(o);
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
        }

        public virtual DataSet ExecuteDataSet(string sql, object param = null, CommandType? commandType = null)
        {
            DataSet ds = new DataSet();
            Exception exception = null;
            var executingArgs = OnExecuting(sql, param, ExecuteType.Query);
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                using (IDbCommand cmd = GetCommand(sql, param, commandType, _Transaction))
                {
                    IDataAdapter adapter = GetAdapter(cmd);
                    adapter.Fill(ds);
                    cmd.Parameters?.Clear();
                }
            }
            catch (Exception ex)
            {
                exception = ex;
                throw;
            }
            finally
            {
                if (wasClosed) _Connection.Close();
                OnExecuted(executingArgs, exception, null);
            }
            return ds;
        }

        protected abstract IDbCommand GetCommand(string sql, object param = null, CommandType? commandType = null, IDbTransaction transaction = null);
        protected abstract IDataAdapter GetAdapter(IDbCommand command);
        public abstract int BulkInsert(DataTable table, string tableName);
        public abstract Task<int> BulkInsertAsync(DataTable table, string tableName);

        protected virtual string ParamToString(object param)
        {
            if (param == null) return "";
            IDynamicParameters dynamicParameters = null;
            if (CommonUtils.IsDynamicParameter(param))
            {
                dynamicParameters = CommonUtils.GetDynamicParameters(param);
            }
            else if (param is IDynamicParameters)
            {
                dynamicParameters = (param as IDynamicParameters);
            }

            if (dynamicParameters == null)
            {
                throw new ArgumentNullException("parameter error");
            }
            else
            {
                return dynamicParameters.ToString();
            }
        }

        protected virtual void SetupParam(IDbCommand cmd, object param)
        {
            if (param == null) return;
            IDynamicParameters dynamicParameters = null;
            if (CommonUtils.IsDynamicParameter(param))
            {
                dynamicParameters = CommonUtils.GetDynamicParameters(param);
            }
            else if (param is IDynamicParameters)
            {
                dynamicParameters = (param as IDynamicParameters);
            }

            if (dynamicParameters == null)
            {
                throw new ArgumentNullException("parameter error");
            }
            else
            {
                dynamicParameters.AddParameters(cmd);
            }
        }

        #region 事务
        public void BeginTransaction(IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            if (_Connection.State == ConnectionState.Closed) _Connection.Open();
            if (_Transaction == null)
            {
                _Transaction = _Connection.BeginTransaction(isolationLevel);
            }
        }

        public void CommitTransaction()
        {
            _Transaction?.Commit();
            _Transaction = null;
            if (_Connection.State == ConnectionState.Open) _Connection.Close();
        }

        public void RollbackTransaction()
        {
            _Transaction?.Rollback();
            _Transaction = null;
            if (_Connection.State == ConnectionState.Open) _Connection.Close();
        }

        public RunTransactionResult RunTransaction(Action action, IsolationLevel isolationLevel = IsolationLevel.ReadCommitted)
        {
            RunTransactionResult runTransactionResult = new RunTransactionResult()
            {
                Success = true
            };
            bool wasClosed = _Connection.State == ConnectionState.Closed;
            try
            {
                if (wasClosed) _Connection.Open();
                BeginTransaction(isolationLevel);
                action();
                CommitTransaction();
            }
            catch (Exception ex)
            {
                runTransactionResult.Success = false;
                runTransactionResult.Exception = ex;
                RollbackTransaction();
            }
            finally
            {
                if (wasClosed) _Connection.Close();
            }
            return runTransactionResult;
        }
        #endregion 事务
    }
}